package com.java.dao.impl;

import com.java.bean.Courier;
import com.java.dao.BaseCourierDao;
import com.java.exception.DuplicateException;
import com.java.util.DBUtil;
import com.java.exception.ExceptionStateEnum;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CourierDaoMysql extends DBUtil implements BaseCourierDao {
                                                                                                                                                                                    // language=MySQL
    private static final String SQL_CONSOLE =               "SELECT COUNT(1) SIZE,COUNT(TO_DAYS(REGTIME)=TO_DAYS(NOW()) OR NULL) DAY FROM couriers WHERE NOT STATUS=1;      ";      // language=MySQL
    private static final String SQL_LIST_COURIER =          "SELECT * FROM couriers WHERE NOT STATUS=1 LIMIT ?,?;                                                           ";      // language=MySQL
    private static final String SQL_GET_COURIER_BY_PHONE =  "SELECT * FROM couriers WHERE PHONE=? AND NOT STATUS=1;                                                         ";      // language=MySQL
    private static final String SQL_LOGIN =                 "SELECT * FROM couriers WHERE NAME=? AND PASSWORD=? AND STATUS=0;                                               ";      // language=MySQL
    private static final String SQL_INSERT =                "INSERT INTO couriers VALUES(NULL,?,?,?,?,0,?,0,?,?,?);                                                         ";      // language=MySQL
    private static final String SQL_UPDATE =                "UPDATE couriers SET NAME=?,PHONE=?,IDENTIFICATION=?,PASSWORD=?,DISPATCHES=?,EMAIL=?,STATUS=?,ADDR=? WHERE ID=?;";      // language=MySQL
    private static final String SQL_DELETE =                "UPDATE couriers SET STATUS=1,PHONE=NULL,IDENTIFICATION=NULL,EMAIL=NULL WHERE ID=?;                             ";      // language=MySQL
    private static final String SQL_UPDATE_LOGINTIME =      "UPDATE couriers SET LOGINTIME=NOW() WHERE ID=?;                                                                ";      // language=MySQL
    private static final String SQL_GET_COURIER_BY_EMAIL =  "SELECT * FROM couriers WHERE EMAIL=?;                                                                          ";      // language=MySQL
    private static final String SQL_UPDATE_ADD_DISPATCHES = "UPDATE couriers SET DISPATCHES=DISPATCHES+1 WHERE ID=?;                                                        ";

    /**
     * 控制台信息
     *
     * @return [{"size":XX,"day":XX}]
     */
    @Override
    public Map<String, Integer> console() {
        try {
            query(pps(SQL_CONSOLE), null);
            if (rs.next()) {
                Map<String, Integer> data = new HashMap<>();
                data.put("size", rs.getInt("SIZE"));
                data.put("day", rs.getInt("DAY"));
                return data;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }

    /**
     * 列出一定数量的快递员信息
     *
     * @param offset     偏移量
     * @param pageNumber 数量
     * @return 快递员集合
     */
    @Override
    public List<Courier> listCourier(int offset, int pageNumber) {
        try {
            List<Object> params = new ArrayList<>();
            params.add(offset);
            params.add(pageNumber);
            query(pps(SQL_LIST_COURIER), params);
            return setAll(Courier.class);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }

    /**
     * 新增快递员
     *
     * @param courier 快递员信息 要求传入：name phone identification password email regtime logintime
     * @return 生成的键
     * @exception DuplicateException, code: cphone_u (-2)   identify_u (-3)   cemail_u (-4)
     */
    @Override
    public int insert(Courier courier) throws DuplicateException {
        try {
            List<Object> params = new ArrayList<>();
            params.add(courier.getName());
            params.add(courier.getPhone());
            params.add(courier.getIdentification());
            params.add(courier.getPassword());
            params.add(courier.getEmail());
            params.add(courier.getRegtime());
            params.add(courier.getLogintime());
            params.add(courier.getAddr());
            update(pps(SQL_INSERT, Statement.RETURN_GENERATED_KEYS), params);
            rs = pps.getGeneratedKeys();
            return getCount();
        } catch (SQLException e) {
            throwException(e.getMessage());
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return -1;
    }

    private void throwException(String message) throws DuplicateException {
        if (message.contains("phone_u")) {
            throw new DuplicateException(ExceptionStateEnum.DUPLICATE_PHONE);
        } else if (message.contains("identify_u")) {
            throw new DuplicateException(ExceptionStateEnum.DUPLICATE_IDENTIFICATION);
        } else if (message.contains("email_u")) {
            throw new DuplicateException(ExceptionStateEnum.DUPLICATE_EMAIL);
        }
    }

    /**
     * 根据快递员手机号码获取快递员信息
     *
     * @param cphone 快递员手机号码
     * @return 快递员信息
     */
    @Override
    public Courier getCourierByPhone(String cphone) {
        try {
            List<Object> param = new ArrayList<>();
            param.add(cphone);
            query(getConnection().prepareStatement(SQL_GET_COURIER_BY_PHONE), param);
            return setOne(Courier.class);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }

    /**
     * 更新快递员信息
     *
     * @param id      快递员id
     * @param courier 新的快递员信息 要求传入：name phone identification password dispatches email
     * @return 影响行数
     */
    @Override
    public int update(int id, Courier courier) throws DuplicateException {
        try {
            List<Object> params = new ArrayList<>();
            params.add(courier.getName());
            params.add(courier.getPhone());
            params.add(courier.getIdentification());
            params.add(courier.getPassword());
            params.add(courier.getDispatches());
            params.add(courier.getEmail());
            params.add(courier.getStatus());
            params.add(courier.getAddr());
            params.add(id);
            return update(pps(SQL_UPDATE), params);
        } catch (SQLException e) {
            throwException(e.getMessage());
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return -1;
    }

    /**
     * 删除快递员信息（假删除）
     *
     * @param id 快递员id
     * @return 影响行数
     */
    @Override
    public int delete(int id) {
        try {
            return update(pps(SQL_DELETE), id);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return -1;
    }

    /**
     * 快递员增加一个派件数
     *
     * @param id 快递员id
     * @return true 成功 false 失败
     */
    @Override
    public boolean addDispatches(int id) {
        try {
            return update(pps(SQL_UPDATE_ADD_DISPATCHES), id) == 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return false;
    }

    /**
     * 快递员登录
     *
     * @param name     姓名
     * @param password 密码
     * @return 成功返回快递员信息， 否则返回null
     */
    @Override
    public Courier login(String name, String password) {
        Courier result = null;
        try {
            List<Object> params = new ArrayList<>();
            params.add(name);
            params.add(password);
            query(pps(SQL_LOGIN), params);
            result = setOne(Courier.class);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        if (result != null) {
            try {
                update(pps(SQL_UPDATE_LOGINTIME), result.getId());
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                closeAll();
            }
        }
        return result;
    }

    @Override
    public Courier getCourierByEmail(String email) {
        try {
            query(pps(SQL_GET_COURIER_BY_EMAIL), email);
            return setOne(Courier.class);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }
}
